﻿using System;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Nortec.Security.BusinessEntities;
using System.Configuration.Provider;
using System.Collections;


namespace Nortec.Security.DataObjects
{
    public class DOSecurity
    {
        #region Declarations
        private static Database db;

        //StoreProcNames
        private const string ADD_USER_ROLE = "userRoleAdd";
        private const string ADD_USER_ROLE_WITH_IDS = "userRoleAddWithIds";
        private const string ADD_RIGHT_TO_ROLE = "rightToRoleAdd";
        private const string GET_COMPANIES_BY_APP_NAME = "companyGetByAppName";
        private const string GET_BRANCHES_BY_COMPANY = "branchGetByCompany";
        private const string GET_APPLICATION_ID = "applicationGetId";
        private const string ADD_ROLE = "roleAdd";
        private const string ADD_USER = "userAdd";
        private const string UPDATE_ROLE = "roleUpdate";
        private const string DELETE_ROLE = "roleDelete";
        private const string UPDATE_USER = "userUpdate";
        private const string DELETE_USER = "userDelete";
        private const string DELETE_RIGHTS_FROM_ROLE = "rightFromRoleDelete";
        private const string DELETE_ROLES_FROM_USER = "rolesFromUserDelete";
        private const string FIND_USER_IN_ROLE = "userFindInRole";
        private const string FIND_ALL_ROLES = "roleFindAll";
        private const string FIND_USER_ROLES = "roleFindByUser";
        private const string GET_USERS_IN_ROLE = "usersGetInRole";
        private const string IS_USER_IN_ROLE = "userIsInRole";
        private const string DELETE_USER_ROLE = "userRoleDelete";
        private const string FIND_ROLE_BY_NAME = "roleFindByName";
        private const string FIND_USER_BY_NAME = "userFindByName";
        private const string FIND_USER_RIGHTS = "rightFindByUser";
        private const string FIND_RIGHT_BY_TYPE = "rightFindByType";
        private const string FIND_ROLE_RIGHTS = "rightFindByRole";
        private const string FIND_ALL_USERS = "userFindAll";
        #endregion

        #region Properties: Non-Public

        protected static Database DB
        {
            get
            {
                if (db == null)
                    db = DatabaseFactory.CreateDatabase();
                return db;
            }
        }

        #endregion

        #region Properties: Public
        public void AddUserToRole(string username, string roleName, string applicationName, string companyName)
        {
            DbCommand command = DB.GetStoredProcCommand(ADD_USER_ROLE);

            DB.AddInParameter(command, "@UserName", DbType.String, username);
            DB.AddInParameter(command, "@RoleName", DbType.String, roleName);
            DB.AddInParameter(command, "@ApplicationName", DbType.String, username);
            DB.AddInParameter(command, "@CompanyName", DbType.String, roleName);

            DB.ExecuteNonQuery(command);
        }
        public void AddUserToRole(int userId, int roleId, DbTransaction t)
        {
            DbCommand command = DB.GetStoredProcCommand(ADD_USER_ROLE_WITH_IDS);
            DB.AddInParameter(command, "@user_id", DbType.Int32, userId);
            DB.AddInParameter(command, "@role_id", DbType.Int32, roleId);
            DB.ExecuteNonQuery(command, t);
        }
        public BEEmpresa.EmpresaDataTable GetCompanies(string applicationName)
        {
            var companies = new BEEmpresa();
            DbCommand command = DB.GetStoredProcCommand(GET_COMPANIES_BY_APP_NAME);
            DB.AddInParameter(command, "@app_name", DbType.String, applicationName);

            DataSet dataset = DB.ExecuteDataSet(command);

            CopyDataSetInformation(dataset, companies);
            return companies.Empresa;
        }
        public BESucursal.SucursalDataTable GetBranches(int companyId)
        {
            var branches = new BESucursal();
            DbCommand command = DB.GetStoredProcCommand(GET_BRANCHES_BY_COMPANY);
            DB.AddInParameter(command, "@company_id", DbType.Int32, companyId);

            DataSet dataset = DB.ExecuteDataSet(command);

            CopyDataSetInformation(dataset, branches);
            return branches.Sucursal;
        }
        public int GetApplicationId(string applicationName, int companyId)
        {
            DbCommand command = DB.GetStoredProcCommand(GET_APPLICATION_ID);
            DB.AddInParameter(command, "@app_name", DbType.String, applicationName);
            DB.AddInParameter(command, "@company_id", DbType.Int32, companyId);

            object result = DB.ExecuteScalar(command);
            if (DBNull.Value != result)
                return (int)result;
            return 0;
        }
        public int CreateRole(string roleName, string roleDescription, int applicationId)
        {
            DbCommand command = DB.GetStoredProcCommand(ADD_ROLE);
            DB.AddInParameter(command, "@role_name", DbType.String, roleName);
            DB.AddInParameter(command, "@role_desc", DbType.String, roleDescription);
            DB.AddInParameter(command, "@app_id", DbType.Int32, applicationId);

            object result = DB.ExecuteScalar(command);
            if (DBNull.Value == result)
                throw new ProviderException("Se encontró un error inesperado al agregar el nuevo rol.");
            
            return Convert.ToInt32(result);
        }
        public int CreateRole(string roleName, string roleDescription, int applicationId, DbTransaction t)
        {
            DbCommand command = DB.GetStoredProcCommand(ADD_ROLE);
            DB.AddInParameter(command, "@role_name", DbType.String, roleName);
            DB.AddInParameter(command, "@role_desc", DbType.String, roleDescription);
            DB.AddInParameter(command, "@app_id", DbType.Int32, applicationId);

            object result = DB.ExecuteScalar(command, t);
            if (DBNull.Value == result)
                throw new ProviderException("Se encontró un error inesperado al agregar el nuevo rol.");

            return Convert.ToInt32(result);
        }
        public int CreateRole(string roleName, string roleDescription, int applicationId, ArrayList rights)
        {

            using (DbConnection conn = DB.CreateConnection())
            {
                int rightId;
                int newRoleId;
                bool error = false;

                conn.Open();
                DbTransaction t = conn.BeginTransaction();

                try
                {
                    newRoleId = CreateRole(roleName, roleDescription, applicationId, t);

                    if (newRoleId > 0)
                    {
                        for (int i = 0; i < rights.Count; i++)
                        {
                            rightId = Convert.ToInt32(rights[i]);
                            AddRightToRole(rightId, newRoleId, t);
                        }
                    }
                    else error = true;
                }
                catch (Exception)
                {
                    error = true;
                    newRoleId = -1;
                }

                if (error)
                    t.Rollback();
                else
                    t.Commit();

                conn.Close();
                return newRoleId;
            }

        }
        public bool UpdateRole(string roleName, string roleDescription, int roleId, DbTransaction t)
        {
            DbCommand command = DB.GetStoredProcCommand(UPDATE_ROLE);
            DB.AddInParameter(command, "@role_name", DbType.String, roleName);
            DB.AddInParameter(command, "@role_desc", DbType.String, roleDescription);
            DB.AddInParameter(command, "@role_id", DbType.Int32, roleId);

            object result = DB.ExecuteScalar(command, t);
            if (DBNull.Value == result)
                throw new ProviderException("Se encontró un error inesperado al actualizar el rol.");

            return Convert.ToBoolean(result);
        }
        public bool RemoveAllRightsFromRole(int roleId, DbTransaction t)
        {
            DbCommand command = DB.GetStoredProcCommand(DELETE_RIGHTS_FROM_ROLE);
            DB.AddInParameter(command, "@role_id", DbType.Int32, roleId);
            
            object result = DB.ExecuteScalar(command, t);
            if (DBNull.Value == result)
                throw new ProviderException("Se encontró un error inesperado al actualizar el rol.");

            return Convert.ToBoolean(result);
        }
        public bool UpdateRole(string roleName, string roleDescription, int roleId, ArrayList rights)
        {
            using (DbConnection conn = DB.CreateConnection())
            {
                int rightId;
                bool success;

                conn.Open();
                DbTransaction t = conn.BeginTransaction();

                try
                {
                    success = UpdateRole(roleName, roleDescription, roleId, t);

                    if (success)
                        success = RemoveAllRightsFromRole(roleId, t);

                    if (success)
                    {
                        for (int i = 0; i < rights.Count; i++)
                        {
                            rightId = Convert.ToInt32(rights[i]);
                            AddRightToRole(rightId, roleId, t);
                        }
                    }
                }
                catch (Exception)
                {
                    success = false;
                }

                if (!success)
                    t.Rollback();
                else
                    t.Commit();

                conn.Close();
                return success;
            }

        }
        public void AddRightToRole(int rightId, int roleId, DbTransaction t)
        {
            DbCommand command = DB.GetStoredProcCommand(ADD_RIGHT_TO_ROLE);
            DB.AddInParameter(command, "@right_id", DbType.Int32, rightId);
            DB.AddInParameter(command, "@role_id", DbType.Int32, roleId);
            DB.ExecuteNonQuery(command, t);
        }
        public bool DeleteRole(string roleName, int applicationId, bool throwOnPopulatedRole)
        {
            DbCommand command = DB.GetStoredProcCommand(DELETE_ROLE);
            DB.AddInParameter(command, "@role_name", DbType.String, roleName);
            DB.AddInParameter(command, "@app_id", DbType.Int32, applicationId);
            DB.AddInParameter(command, "@throw", DbType.Boolean, throwOnPopulatedRole);

            object result = DB.ExecuteScalar(command);
            if (DBNull.Value == result)
                throw new Exception("Se encontró un error inesperado al borrar el rol");
            return Convert.ToBoolean(result);
        }
        public BEUsuario.UsuarioDataTable FindUsersInRole(string roleName, string usernameToMatch, int applicationId)
        {
            var usuarios = new BEUsuario();
            DbCommand command = DB.GetStoredProcCommand(FIND_USER_IN_ROLE);
            DB.AddInParameter(command, "@role_name", DbType.String, roleName);
            DB.AddInParameter(command, "@user_match", DbType.String, usernameToMatch);
            DB.AddInParameter(command, "@app_id", DbType.Int32, applicationId);

            DataSet dataset = DB.ExecuteDataSet(command);

            CopyDataSetInformation(dataset, usuarios);

            return usuarios.Usuario;
        }
        public BERol.RolDataTable GetAllRoles(int applicationId)
        {
            var roles = new BERol();
            DbCommand command = DB.GetStoredProcCommand(FIND_ALL_ROLES);
            DB.AddInParameter(command, "@app_id", DbType.Int32, applicationId);

            DataSet dataset = DB.ExecuteDataSet(command);

            CopyDataSetInformation(dataset, roles);

            return roles.Rol;
        }
        public BERol.RolDataTable GetRolesForUser(string username, int applicationId)
        {
            var roles = new BERol();
            DbCommand command = DB.GetStoredProcCommand(FIND_USER_ROLES);
            DB.AddInParameter(command, "@usr_name", DbType.String, username);
            DB.AddInParameter(command, "@app_id", DbType.Int32, applicationId);

            DataSet dataset = DB.ExecuteDataSet(command);

            CopyDataSetInformation(dataset, roles);

            return roles.Rol;
        }
        public BEUsuario.UsuarioDataTable GetUsersInRole(string roleName, int applicationId)
        {
            var usuarios = new BEUsuario();
            DbCommand command = DB.GetStoredProcCommand(GET_USERS_IN_ROLE);
            DB.AddInParameter(command, "@role_name", DbType.String, roleName);
            DB.AddInParameter(command, "@app_id", DbType.Int32, applicationId);

            DataSet dataset = DB.ExecuteDataSet(command);

            // Pasa informacion hacia el BE
            if (dataset != null && dataset.Tables.Count > 0)
            {
                dataset.Tables[0].TableName = usuarios.Tables[0].TableName;
                usuarios.Merge(dataset);
                usuarios.EnforceConstraints = true;
            }
            return usuarios.Usuario;
        }
        public bool IsUserInRole(string username, string roleName, int applicationId)
        {
            DbCommand command = DB.GetStoredProcCommand(IS_USER_IN_ROLE);
            DB.AddInParameter(command, "@user_name", DbType.String, username);
            DB.AddInParameter(command, "@role_name", DbType.String, roleName);
            DB.AddInParameter(command, "@app_id", DbType.Int32, applicationId);

            object result = DB.ExecuteScalar(command);
            if (DBNull.Value != result)
            {
                int resultInt = Convert.ToInt32(result);
                if (resultInt == -1)
                    throw new ProviderException("El rol o usuario especificado no existe");
                return Convert.ToBoolean(result);
            }
            throw new Exception("Se encontró un error inesperado estableciendo comunicación con la base de datos");
        }
        public bool RemoveUserFromRole(string username, string roleName, int applicationId, DbTransaction t)
        {

            DbCommand command = DB.GetStoredProcCommand(DELETE_USER_ROLE);
                        
            DB.AddInParameter(command, "@user_name", DbType.String, username);
            DB.AddInParameter(command, "@role_name", DbType.String, roleName);
            DB.AddInParameter(command, "@app_id", DbType.Int32, applicationId);
            
            object result = DB.ExecuteScalar(command, t);
            if (DBNull.Value == result)
                return true;
            return Convert.ToBoolean(result);
        }
        public bool RemoveUserFromRoles(string username, string[] roleNames, int applicationId, DbTransaction t)
        {
            bool error = false;
            try
            {
                foreach (string roleName in roleNames)
                {
                    error = RemoveUserFromRole(username, roleName, applicationId, t);
                    if (error)
                        break;
                }
            }
            catch (Exception)
            {
                error = true;
            }
            return error;
        }
        public bool RemoveUsersFromRoles(string[] usernames, string[] roleNames, int applicationId)
        {
            bool error = false;
            using (DbConnection conn = DB.CreateConnection()){
                conn.Open();
                DbTransaction t = conn.BeginTransaction();

                try
                {
                    foreach (string username in usernames)
                    {
                        error = RemoveUserFromRoles(username, roleNames, applicationId, t);
                        if (error)
                            break;
                    }
                }
                catch (Exception)
                {
                    error = true;
                }

                if (error)
                    t.Rollback();
                else
                    t.Commit();

                conn.Close();
            }

            //Returns true is there was no error
            return !error;
        }
        public BERol.RolDataTable GetRole(string roleName, int applicationId)
        {
            var roles = new BERol();
            DbCommand command = DB.GetStoredProcCommand(FIND_ROLE_BY_NAME);
            DB.AddInParameter(command, "@role_name", DbType.String, roleName);
            DB.AddInParameter(command, "@app_id", DbType.Int32, applicationId);

            DataSet dataset = DB.ExecuteDataSet(command);

            CopyDataSetInformation(dataset, roles);

            return roles.Rol;
        }
        public BEUsuario.UsuarioDataTable GetUserByName(string username, int applicationId)
        {
            var usuarios = new BEUsuario();
            DbCommand command = DB.GetStoredProcCommand(FIND_USER_BY_NAME);
            DB.AddInParameter(command, "@username", DbType.String, username);
            DB.AddInParameter(command, "@app_id", DbType.Int32, applicationId);

            DataSet dataset = DB.ExecuteDataSet(command);

            CopyDataSetInformation(dataset, usuarios);

            return usuarios.Usuario;
        }
        public BEUsuario.UsuarioDataTable GetAllUsers(int applicationId)
        {
            var usuarios = new BEUsuario();
            DbCommand command = DB.GetStoredProcCommand(FIND_ALL_USERS);
            DB.AddInParameter(command, "@app_id", DbType.Int32, applicationId);

            DataSet dataset = DB.ExecuteDataSet(command);

            CopyDataSetInformation(dataset, usuarios);

            return usuarios.Usuario;
        }
        public BEPrivilegio.PrivilegioDataTable GetRightsForUser(string username, int applicationId)
        {
            var rights = new BEPrivilegio();
            DbCommand command = DB.GetStoredProcCommand(FIND_USER_RIGHTS);
            DB.AddInParameter(command, "@usr_name", DbType.String, username);
            DB.AddInParameter(command, "@app_id", DbType.Int32, applicationId);

            DataSet dataset = DB.ExecuteDataSet(command);

            CopyDataSetInformation(dataset, rights);
            
            return rights.Privilegio;
        }
        public BEPrivilegio.PrivilegioDataTable GetRightsForRole(string roleName, int applicationId)
        {
            var rights = new BEPrivilegio();
            DbCommand command = DB.GetStoredProcCommand(FIND_ROLE_RIGHTS);
            DB.AddInParameter(command, "@role_name", DbType.String, roleName);
            DB.AddInParameter(command, "@app_id", DbType.Int32, applicationId);

            DataSet dataset = DB.ExecuteDataSet(command);

            CopyDataSetInformation(dataset, rights);

            return rights.Privilegio;
        }
        public BEPrivilegio.PrivilegioDataTable GetRights(int applicationId, string type)
        {
            var rights = new BEPrivilegio();
            DbCommand command = DB.GetStoredProcCommand(FIND_RIGHT_BY_TYPE);
            DB.AddInParameter(command, "@app_id", DbType.Int32, applicationId);
            DB.AddInParameter(command, "@type", DbType.String, type);

            DataSet dataset = DB.ExecuteDataSet(command);

            CopyDataSetInformation(dataset, rights);
                        
            return rights.Privilegio;
        }
        private static void CopyDataSetInformation(DataSet origin, DataSet destiny)
        {
            if (origin != null && origin.Tables.Count > 0)
            {
                origin.Tables[0].TableName = destiny.Tables[0].TableName;
                destiny.Merge(origin);
                destiny.EnforceConstraints = true;
            }
        }
        public bool DeleteUser(int userId)
        {
            DbCommand command = DB.GetStoredProcCommand(DELETE_USER);
            DB.AddInParameter(command, "@user_id", DbType.Int32, userId);
            
            object result = DB.ExecuteScalar(command);
            if (DBNull.Value == result)
                throw new Exception("Se encontró un error inesperado al borrar el rol");
            return Convert.ToBoolean(result);
        }
        public int CreateUser(string userName, string password, string firstName, string lastName, int applicationId, DbTransaction t)
        {
            DbCommand command = DB.GetStoredProcCommand(ADD_USER);
            DB.AddInParameter(command, "@user_first_name", DbType.String, firstName);
            DB.AddInParameter(command, "@user_last_name", DbType.String, lastName);
            DB.AddInParameter(command, "@user_name", DbType.String, userName);
            DB.AddInParameter(command, "@user_pwd", DbType.String, password);
            DB.AddInParameter(command, "@app_id", DbType.Int32, applicationId);

            object result = DB.ExecuteScalar(command, t);
            if (DBNull.Value == result)
                throw new ProviderException("Se encontró un error inesperado al agregar el nuevo usuario.");

            return Convert.ToInt32(result);
        }
        public int CreateUser(string userName, string password, string firstName, string lastName, int applicationId, ArrayList roles)
        {

            using (DbConnection conn = DB.CreateConnection())
            {
                int roleId;
                int newUserId;
                bool error = false;

                conn.Open();
                DbTransaction t = conn.BeginTransaction();

                try
                {
                    newUserId = CreateUser(userName, password, firstName, lastName, applicationId, t);

                    if (newUserId > 0)
                    {
                        for (int i = 0; i < roles.Count; i++)
                        {
                            roleId = Convert.ToInt32(roles[i]);
                            AddUserToRole(newUserId, roleId, t);
                        }
                    }
                    else error = true;
                }
                catch (Exception)
                {
                    error = true;
                    newUserId = -1;
                }

                if (error)
                    t.Rollback();
                else
                    t.Commit();

                conn.Close();
                return newUserId;
            }

        }
        public bool RemoveAllRolesFromUser(int userId, DbTransaction t)
        {
            DbCommand command = DB.GetStoredProcCommand(DELETE_ROLES_FROM_USER);
            DB.AddInParameter(command, "@user_id", DbType.Int32, userId);

            object result = DB.ExecuteScalar(command, t);
            if (DBNull.Value == result)
                throw new ProviderException("Se encontró un error inesperado al actualizar el usuario.");

            return Convert.ToBoolean(result);
        }
        public bool UpdateUser(string userName, string password, string firstName, string lastName, int userId, DbTransaction t)
        {
            DbCommand command = DB.GetStoredProcCommand(UPDATE_USER);
            DB.AddInParameter(command, "@user_name", DbType.String, userName);
            DB.AddInParameter(command, "@user_pwd", DbType.String, password);
            DB.AddInParameter(command, "@first_name", DbType.String, firstName);
            DB.AddInParameter(command, "@last_name", DbType.String, lastName);
            DB.AddInParameter(command, "@user_id", DbType.Int32, userId);

            object result = DB.ExecuteScalar(command, t);
            if (DBNull.Value == result)
                throw new ProviderException("Se encontró un error inesperado al actualizar el usuario.");

            return Convert.ToBoolean(result);
        }
        public bool UpdateUser(string userName, string password, string firstName, string lastName, int userId, ArrayList roles)
        {
            using (DbConnection conn = DB.CreateConnection())
            {
                int roleId;
                bool success;

                conn.Open();
                DbTransaction t = conn.BeginTransaction();

                try
                {
                    success = UpdateUser(userName, password, firstName, lastName, userId, t);

                    if (success)
                        success = RemoveAllRolesFromUser(userId, t);

                    if (success)
                    {
                        for (int i = 0; i < roles.Count; i++)
                        {
                            roleId = Convert.ToInt32(roles[i]);
                            AddUserToRole(userId, roleId, t);
                        }
                    }
                }
                catch (Exception)
                {
                    success = false;
                }

                if (!success)
                    t.Rollback();
                else
                    t.Commit();

                conn.Close();
                return success;
            }
        }
        #endregion
    }
}
